import pandas as pd

def combine_row(row, lang):
    lang_y = lang + '_y'
    lang_x = lang + '_x'

    return row[lang_y] if row[lang_x] is not str else row[lang_x]

if __name__ == "__main__":
    ref_list_df = pd.read_table('../ref_list.txt', delimiter='\\t', encoding='utf-8', skiprows=1, engine='python')
    xlsx_df = pd.read_excel('test.xlsx', skiprows=1)
    xlsx_df.rename(
        columns={
            '简体中文': 'Si_Chinese',
            '繁體中文': 'traditional_Chinese',
            '英语': 'English',
            '蒙古语': 'Mongolian',
            '日语': 'Japanese',
            '德语': 'German',
            '西班牙语': 'spanish',
            '法语': 'French',
            '意大利语': 'italian',
            '俄语': 'Russian',
            '荷兰语': 'Netherlands',
            '韩语': 'Korean',
            '泰语': 'Thai',
            '越南语': 'vietnamese',
            '马来语': 'Malay',
            '印尼语': 'Indonesian',
            '葡萄牙语': 'Portuguese',
            '罗马尼亚语': 'Romanian',
            '波兰语': 'Polish',
            '土耳其语': 'turkish'
        }, inplace=True)

    with open('output.txt', 'w') as output_file:
        output_file.write('Number of Language	19	For Customer Reference Only!\n')

    langs = [
        'Enum Value',
        'Module Name',
        'Region ID',
        'Description',
        'Si_Chinese',
        'traditional_Chinese',
        'English',
        'Russian',
        'French',
        'spanish',
        'German',
        'Japanese',
        'italian',
        'Korean',
        'Netherlands',
        'Thai',
        'vietnamese',
        'Malay',
        'Indonesian',
        'Portuguese',
        'Romanian',
        'Polish',
        'turkish',
        'Mongolian'
    ]
    combined_df = pd.merge(ref_list_df, xlsx_df, on='Si_Chinese', how='left')
    for lang in langs:
        if lang not in combined_df.columns:
            combined_df[lang] = combined_df.apply(lambda row: combine_row(row, lang), axis=1)
    # 加载并调整图像尺寸
    combined_df.to_csv(
        'output.txt',
        sep=str("\t"),
        index=False,
        columns=langs,
        mode='a'
    )
